--------------------------------------------------------
--  File created - Friday-December-25-2009   
--------------------------------------------------------
--------------------------------------------------------
--  File updated - Saturday-December-26-2009   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Sequence INCOMPLETE_ALBUM_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "INCOMPLETE_ALBUM_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence MISSING_TRACK_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "MISSING_TRACK_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence ALBUM_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "ALBUM_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence ARTIST_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "ARTIST_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence GENRE_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "GENRE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence TRACK_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "TRACK_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence USER_TRACK_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "USER_TRACK_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

--------------------------------------------------------
--  DDL for Table INCOMPLETE_ALBUM
--------------------------------------------------------

  CREATE TABLE "INCOMPLETE_ALBUM" 
   (	"ID" NUMBER, 
	"ALBUM_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table MISSING_TRACK
--------------------------------------------------------

  CREATE TABLE "MISSING_TRACK" 
   (	"ID" NUMBER, 
	"TRACK_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table ALBUM
--------------------------------------------------------

  CREATE TABLE "ALBUM" 
   (	"ID" NUMBER, 
	"FREEDB_ID" VARCHAR2(4000), 
	"GENRE_ID" NUMBER, 
	"LENGTH" NUMBER, 
	"YEAR" NUMBER, 
	"ARTIST_ID" NUMBER, 
	"TITLE" VARCHAR2(4000), 
	"REVISION" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table ARTIST
--------------------------------------------------------

  CREATE TABLE "ARTIST" 
   (	"ID" NUMBER, 
	"NAME" VARCHAR2(4000)
   ) ;
--------------------------------------------------------
--  DDL for Table GENRE
--------------------------------------------------------

  CREATE TABLE "GENRE" 
   (	"ID" NUMBER, 
	"NAME" VARCHAR2(4000)
   ) ;
--------------------------------------------------------
--  DDL for Table TRACK
--------------------------------------------------------

  CREATE TABLE "TRACK" 
   (	"ID" NUMBER, 
	"TITLE" VARCHAR2(4000), 
	"LENGTH" NUMBER, 
	"ALBUM_ID" NUMBER, 
	"TRACK_NUMBER" NUMBER, 
	"ARTIST_ID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table USER_TRACK
--------------------------------------------------------

  CREATE TABLE "USER_TRACK" 
   (	"ID" NUMBER, 
	"TRACK_ID" NUMBER, 
	"PATH" VARCHAR2(4000), 
	"TITLE" VARCHAR2(4000), 
	"ALBUM_NAME" VARCHAR2(4000), 
	"ARTIST_NAME" VARCHAR2(4000)
   ) ;
--------------------------------------------------------
--  Constraints for Table INCOMPLETE_ALBUM
--------------------------------------------------------

  ALTER TABLE "INCOMPLETE_ALBUM" ADD CONSTRAINT "INCOMPLETE_ALBUM_PK" PRIMARY KEY ("ID") ENABLE;
 
  ALTER TABLE "INCOMPLETE_ALBUM" MODIFY ("ALBUM_ID" NOT NULL ENABLE);
   
--------------------------------------------------------
--  Constraints for Table MISSING_TRACK
--------------------------------------------------------

  ALTER TABLE "MISSING_TRACK" ADD CONSTRAINT "MISSING_TRACK_PK" PRIMARY KEY ("ID") ENABLE;
 
  ALTER TABLE "MISSING_TRACK" MODIFY ("TRACK_ID" NOT NULL ENABLE);
   
--------------------------------------------------------
--  Constraints for Table ALBUM
--------------------------------------------------------

  ALTER TABLE "ALBUM" ADD CONSTRAINT "ALBUM_PK" PRIMARY KEY ("ID") ENABLE;
 
  ALTER TABLE "ALBUM" MODIFY ("TITLE" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table ARTIST
--------------------------------------------------------

  ALTER TABLE "ARTIST" ADD CONSTRAINT "ARTIST_PK" PRIMARY KEY ("ID") ENABLE;
 
  ALTER TABLE "ARTIST" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "ARTIST" MODIFY ("NAME" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table GENRE
--------------------------------------------------------

  ALTER TABLE "GENRE" ADD CONSTRAINT "GENRE_PK" PRIMARY KEY ("ID") ENABLE;
 
  -- ALTER TABLE "GENRE" ADD CONSTRAINT "GENRE_UK1" UNIQUE ("NAME") ENABLE;
 
  ALTER TABLE "GENRE" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "GENRE" MODIFY ("NAME" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table TRACK
--------------------------------------------------------

  ALTER TABLE "TRACK" MODIFY ("TITLE" NOT NULL ENABLE);
 
  ALTER TABLE "TRACK" MODIFY ("ALBUM_ID" NOT NULL ENABLE);
 
  ALTER TABLE "TRACK" MODIFY ("TRACK_NUMBER" NOT NULL ENABLE);
 
  ALTER TABLE "TRACK" ADD CONSTRAINT "TRACK_PK" PRIMARY KEY ("ID") ENABLE;
--------------------------------------------------------
--  Constraints for Table USER_TRACK
--------------------------------------------------------

  ALTER TABLE "USER_TRACK" MODIFY ("ID" NOT NULL ENABLE);
 
  ALTER TABLE "USER_TRACK" MODIFY ("TRACK_ID" NOT NULL DISABLE);
 
  ALTER TABLE "USER_TRACK" MODIFY ("PATH" NOT NULL ENABLE);
 
  ALTER TABLE "USER_TRACK" ADD CONSTRAINT "USER_TRACK_PK" PRIMARY KEY ("ID") ENABLE;
  

  CREATE INDEX "ARTIST_CTX1" ON "ARTIST" ("NAME") INDEXTYPE IS ctxsys.context;
  CREATE INDEX "TRACK_CTX1" ON "TRACK" ("TITLE") INDEXTYPE IS ctxsys.context;
  CREATE INDEX "ALBUM_CTX1" ON "ALBUM" ("TITLE") INDEXTYPE IS ctxsys.context;
  
  
  CREATE INDEX "USER_TRACK_TRACK_ID" ON "USER_TRACK" ("TRACK_ID");
  CREATE INDEX "INCOMPLETE_ALBUM_ALBUM_ID" ON "INCOMPLETE_ALBUM" ("ALBUM_ID");
  CREATE INDEX "MISSING_TRACK_TRACK_ID" ON "MISSING_TRACK" ("TRACK_ID");
  CREATE INDEX "TRACK_ALBUM_ID" ON "TRACK" ("ALBUM_ID");
--------------------------------------------------------
--  DDL for Index ALBUM_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "ALBUM_PK" ON "ALBUM" ("ID") 
  ;
--------------------------------------------------------
--  DDL for Index ARTIST_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "ARTIST_PK" ON "ARTIST" ("ID") 
  ;
--------------------------------------------------------
--  DDL for Index GENRE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "GENRE_PK" ON "GENRE" ("ID") 
  ;
--------------------------------------------------------
--  DDL for Index GENRE_UK1
--------------------------------------------------------

  --CREATE UNIQUE INDEX "GENRE_UK1" ON "GENRE" ("NAME") 
  --;
--------------------------------------------------------
--  DDL for Index IDX_FREEDB_ID
--------------------------------------------------------

  CREATE INDEX "IDX_FREEDB_ID" ON "ALBUM" ("FREEDB_ID") 
  ;
--------------------------------------------------------
--  DDL for Index TRACK_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "TRACK_PK" ON "TRACK" ("ID") 
  ;
--------------------------------------------------------
--  DDL for Index USER_TRACK_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "USER_TRACK_PK" ON "USER_TRACK" ("ID") 
  ;
--------------------------------------------------------
--  Ref Constraints for Table INCOMPLETE_ALBUM
--------------------------------------------------------

  ALTER TABLE "INCOMPLETE_ALBUM" ADD CONSTRAINT "INCOMPLETE_ALBUM_FK" FOREIGN KEY ("ALBUM_ID")
	  REFERENCES "ALBUM" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table MISSING_TRACK
--------------------------------------------------------

  ALTER TABLE "MISSING_TRACK" ADD CONSTRAINT "MISSING_TRACK_FK" FOREIGN KEY ("TRACK_ID")
	  REFERENCES "TRACK" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table ALBUM
--------------------------------------------------------

  ALTER TABLE "ALBUM" ADD CONSTRAINT "ALBUM_FK" FOREIGN KEY ("GENRE_ID")
	  REFERENCES "GENRE" ("ID") ON DELETE SET NULL ENABLE;
 
  ALTER TABLE "ALBUM" ADD CONSTRAINT "ALBUM_FK2" FOREIGN KEY ("ARTIST_ID")
	  REFERENCES "ARTIST" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table TRACK
--------------------------------------------------------

  ALTER TABLE "TRACK" ADD CONSTRAINT "TRACK_FK" FOREIGN KEY ("ALBUM_ID")
	  REFERENCES "ALBUM" ("ID") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table USER_TRACK
--------------------------------------------------------

  ALTER TABLE "USER_TRACK" ADD CONSTRAINT "USER_TRACK_FK" FOREIGN KEY ("TRACK_ID")
	  REFERENCES "TRACK" ("ID") ENABLE; 
--------------------------------------------------------
--  DDL for Trigger BI_INCOMPLETE_ALBUM
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_INCOMPLETE_ALBUM" 
  before insert on "INCOMPLETE_ALBUM"              
  for each row 
begin  
    select "INCOMPLETE_ALBUM_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_INCOMPLETE_ALBUM" ENABLE;
	  
--------------------------------------------------------
--  DDL for Trigger BI_MISSING_TRACK
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_MISSING_TRACK" 
  before insert on "MISSING_TRACK"              
  for each row 
begin  
    select "MISSING_TRACK_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_MISSING_TRACK" ENABLE;
	  
--------------------------------------------------------
--  DDL for Trigger BI_ALBUM
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_ALBUM" 
  before insert on "ALBUM"              
  for each row 
begin  
    select "ALBUM_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_ALBUM" ENABLE;
--------------------------------------------------------
--  DDL for Trigger BI_ARTIST
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_ARTIST" 
  before insert on "ARTIST"              
  for each row 
begin  
    select "ARTIST_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_ARTIST" ENABLE;
--------------------------------------------------------
--  DDL for Trigger BI_GENRE
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_GENRE" 
  before insert on "GENRE"              
  for each row 
begin  
    select "GENRE_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_GENRE" ENABLE;
--------------------------------------------------------
--  DDL for Trigger BI_TRACK
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_TRACK" 
  before insert on "TRACK"              
  for each row 
begin  
    select "TRACK_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_TRACK" ENABLE;
--------------------------------------------------------
--  DDL for Trigger BI_USER_TRACK
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "BI_USER_TRACK" 
  before insert on "USER_TRACK"              
  for each row 
begin  
    select "USER_TRACK_SEQ".nextval into :NEW.ID from dual;
end;
/
ALTER TRIGGER "BI_USER_TRACK" ENABLE; 
